from sqlalchemy import Column, Integer, String, Float, DateTime, ForeignKey, Boolean, Index
from sqlalchemy.orm import relationship
from sqlalchemy.sql import func
from datetime import datetime
from database import Base
from pydantic import BaseModel
from typing import Optional

class CategoryResponse(BaseModel):
    id: str
    name: str
    first_category_id: Optional[str] = None
    first_category_name: Optional[str] = None
    category_type: Optional[str] = None

    class Config:
        from_attributes = True

class Shop(Base):
    __tablename__ = 'shop'
    __table_args__ = {'extend_existing': True}

    id = Column(Integer, primary_key=True, index=True)
    shop_name = Column(String(100), nullable=False, index=True)
    shipping_address = Column(String(100), nullable=True)
    logo_img = Column(String(200), nullable=True)
    shop_address = Column(String(200))
    longitude = Column(String(100), nullable=True)
    latitude = Column(String(100), nullable=True)
    provincia_level_name = Column(String(100), nullable=True)
    municipa_level_name = Column(String(100), nullable=True)
    district_level_name = Column(String(100), nullable=True)
    region_name = Column(String(100), nullable=True)
    enabled = Column(String(200), nullable=True)
    final_exec_timestamp = Column(DateTime, default=datetime.utcnow, nullable=True)
    item_count = Column(String(100), nullable=True)
    present_state = Column(String(100), nullable=True)
    created_at = Column(DateTime, default=datetime.utcnow, nullable=True)
    store_id = Column(Integer, nullable=True)
    
    # 关联关系
    products = relationship("Product", back_populates="shop")

    # 添加复合索引
    __table_args__ = (
        Index('idx_shop_search', 'shop_name', 'region_name', 'enabled'),
    )

class Category(Base):
    __tablename__ = 'category'
    __table_args__ = {'extend_existing': True}

    category_id = Column(String(200), primary_key=True)
    category_name = Column(String(200))
    first_category_id = Column(String(200))
    first_category_name = Column(String(200))
    category_type = Column(String(200))

    def to_response(self) -> CategoryResponse:
        return CategoryResponse(
            id=self.category_id,
            name=self.category_name,
            first_category_id=self.first_category_id,
            first_category_name=self.first_category_name,
            category_type=self.category_type
        )

class Product(Base):
    __tablename__ = 'item'
    __table_args__ = {'extend_existing': True}

    id = Column(Integer, primary_key=True, autoincrement=True)
    run_id = Column(Integer, index=True, nullable=False)
    sku_id = Column(String(64), index=True, nullable=False)
    cleaned_name = Column(String(255))
    item_brand = Column(String(128))
    base_num = Column(Float)
    base_unit = Column(String(32))
    item_specs = Column(String(255))
    package_type = Column(String(64))
    item_classification = Column(String(128))
    item_price = Column(Float)
    base_price = Column(Float)
    item_img = Column(String(255))
    item_name = Column(String(255))
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    shop_name = Column(String(255))
    shop_id = Column(Integer, ForeignKey('shop.id'))
    shop_store_id = Column(Integer, nullable=True)
    
    # 关联关系
    shop = relationship("Shop", back_populates="products")

class BaseConfiguration(Base):
    __tablename__ = 'base_configuration'
    __table_args__ = {'extend_existing': True}

    id = Column(Integer, primary_key=True)
    login_id = Column(String(100), nullable=False)
    device_id = Column(String(100), nullable=False)
    v = Column(String(100), nullable=False)
    app_version = Column(String(100), nullable=False)
    request_version = Column(String(100), nullable=False)
    free_version = Column(String(100), nullable=False)
    session_id = Column(String(255))
    uuid = Column(String(255))
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

